﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class D_Khach:AccessData
    {
        //-----Hàm lấy ra hết------        
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Khach_Get_List";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm lấy theo PK KhachID------
        public M_Khach GetbyPK_KhachID(int KhachID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Khach_GetByKhachId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@KhachID", KhachID);
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                M_Khach M_Khach = null;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        M_Khach = new M_Khach();

                        M_Khach.KhachID = Convert.ToInt32(dr["KhachID"]);
                        if (dr["Diachi"] != DBNull.Value)
                            M_Khach.Diachi = Convert.ToString(dr["Diachi"]);
                        if (dr["Gioitinh"] != DBNull.Value)
                            M_Khach.Gioitinh = Convert.ToString(dr["Gioitinh"]);
                        if (dr["Hovaten"] != DBNull.Value)
                            M_Khach.Hovaten = Convert.ToString(dr["Hovaten"]);
                        if (dr["Ngaysinh"] != DBNull.Value)
                            M_Khach.Ngaysinh = Convert.ToString(dr["Ngaysinh"]);
                        if (dr["Quoctich"] != DBNull.Value)
                            M_Khach.Quoctich = Convert.ToString(dr["Quoctich"]);
                        if (dr["SoCMND"] != DBNull.Value)
                            M_Khach.SoCMND = Convert.ToString(dr["SoCMND"]);
                        if (dr["Sodienthoai"] != DBNull.Value)
                            M_Khach.Sodienthoai = Convert.ToString(dr["Sodienthoai"]);                        
                    }
                }
                dr.Close();
                sqlCmd.Connection.Close();
                return M_Khach;
            }
        }        

        //-----Hàm Insert------
        public void Insert(M_Khach M_Khach)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Khach_Insert";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Khach.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@Hovaten", SqlDbType.NVarChar, 200)).Value = M_Khach.Hovaten;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaysinh", SqlDbType.NVarChar, 50)).Value = M_Khach.Ngaysinh;
            sqlCmd.Parameters.Add(new SqlParameter("@SoCMND", SqlDbType.NVarChar, 50)).Value = M_Khach.SoCMND;
            sqlCmd.Parameters.Add(new SqlParameter("@Gioitinh", SqlDbType.NVarChar, 50)).Value = M_Khach.Gioitinh;
            sqlCmd.Parameters.Add(new SqlParameter("@Sodienthoai", SqlDbType.NVarChar, 250)).Value = M_Khach.Sodienthoai;
            sqlCmd.Parameters.Add(new SqlParameter("@Diachi", SqlDbType.NVarChar, 500)).Value = M_Khach.Diachi;
            sqlCmd.Parameters.Add(new SqlParameter("@Quoctich", SqlDbType.NVarChar, 50)).Value = M_Khach.Quoctich;            

            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
        }

        //-----Hàm Update------
        public bool Update(M_Khach M_Khach)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Khach_Update";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Khach.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@Hovaten", SqlDbType.NVarChar, 200)).Value = M_Khach.Hovaten;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaysinh", SqlDbType.NVarChar, 50)).Value = M_Khach.Ngaysinh;
            sqlCmd.Parameters.Add(new SqlParameter("@SoCMND", SqlDbType.NVarChar, 50)).Value = M_Khach.SoCMND;
            sqlCmd.Parameters.Add(new SqlParameter("@Gioitinh", SqlDbType.NVarChar, 50)).Value = M_Khach.Gioitinh;
            sqlCmd.Parameters.Add(new SqlParameter("@Sodienthoai", SqlDbType.NVarChar, 250)).Value = M_Khach.Sodienthoai;
            sqlCmd.Parameters.Add(new SqlParameter("@Diachi", SqlDbType.NVarChar, 500)).Value = M_Khach.Diachi;
            sqlCmd.Parameters.Add(new SqlParameter("@Quoctich", SqlDbType.NVarChar, 50)).Value = M_Khach.Quoctich; 

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }

        //-----Hàm xóa------
        public bool Delete(int KhachID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Khach_Delete";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", KhachID));

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }
    }
}
